Imports WRDB.DBProviders

''' <summary>
''' Each WRDB project has a unique connection to a database and specified preferences
''' </summary>
''' <remarks>A separate class is used because, although an instance of WRDB can have only one project active at a time, it is sometimes necessary to open another one to copy tables from, etc.</remarks>
Public Class clsProject
    Implements IDisposable

#Region "Shared Variables"

    ''' <summary>
    ''' Number of seconds before connection timeout error occurs (used when call TestConnectionQuick in clsDBBase)
    ''' </summary>
    Friend Const CONNECTIONTIMEOUT As Integer = 10

    ''' <summary>
    ''' Name of temporary database used in some data providers to better segregate temporary tables from rest of tables (so can make project database readonly and still change temp tables)
    ''' </summary>
    Public Shared TempDBName As String = "TempDB"

    ' ''' <summary>
    ' ''' Name of directory for temporary files (does NOT end with backslash)
    ' ''' </summary>
    'Public Shared TempPath As String = String.Format("{0}\WRDB", My.Computer.FileSystem.SpecialDirectories.Temp)

    Private Shared _TempPath As String = "", _TempPrefix As String = "", _DataPrefix As String = ""

    ''' <summary>
    ''' Name of directory for temporary files (does NOT end with backslash)
    ''' </summary>
    ''' <remarks>ASP.Net does not allow access to SpecialDirectories.Temp; therefore, before doing anything, ASP.Net caller must set using "Server.MapPath("~") and permissions must be set to allow WRDB folder to be created</remarks>
    Public Shared Property TempPath As String
        Get
            If _TempPath = "" Then _TempPath = String.Format("{0}\WRDB", My.Computer.FileSystem.SpecialDirectories.Temp)
            If Not My.Computer.FileSystem.DirectoryExists(_TempPath) Then My.Computer.FileSystem.CreateDirectory(_TempPath)
            Return _TempPath
        End Get
        Set(ByVal value As String)
            _TempPath = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for temporary table names that uniquely identifies the user and session
    ''' </summary>
    ''' <remarks>ASP.Net does not track by loginname; instead, must path unique identifier based on session</remarks>
    Public Shared Property TempPrefix As String
        Get
            If _TempPrefix = "" Then _TempPrefix = String.Format("TEMP_{0}_", Environment.UserName)
            Return _TempPrefix
        End Get
        Set(ByVal value As String)
            _TempPrefix = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for temporary data tables (opened using Open Data File) that uniquely identifies the user and session
    ''' </summary>
    ''' <remarks>ASP.Net does not track by loginname; instead, must path unique identifier based on session</remarks>
    Public Shared Property DataPrefix As String
        Get
            If _DataPrefix = "" Then _DataPrefix = String.Format("DATA_{0}_", Environment.UserName)
            Return _DataPrefix
        End Get
        Set(ByVal value As String)
            _DataPrefix = value
        End Set
    End Property

    ''' <summary>
    ''' Prefix for permanent non-WRDB data tables that must be retained but that you don't want to appear as a Working table
    ''' (for example, ARC application security and forecast tables)
    ''' </summary>
    Public Shared ReadOnly Property OtherPrefix As String
        Get
            Return "OTHR_"
        End Get
    End Property

    ''' <summary>
    ''' Name of table or view containing previous query results; if using neither, will contain subquery SELECT statement
    ''' </summary>
    Public Shared TempData As String

    ''' <summary>
    ''' Name of view containing subquery results for complex queries
    ''' </summary>
    Public Shared TempView As String

    ''' <summary>
    ''' Name of temporary table containing report results
    ''' </summary>
    Public Shared RptData As String

    Public SupportTableStructuresChecked As Boolean = False

#End Region

#Region "Private Variables"

    Public DataSet As New DataSet

    ''' <summary>
    ''' Dataset which holds all table structures; used to create new tables; table names are same as support tables or "Working" 
    ''' </summary>
    ''' <remarks></remarks>
    Public dsWRDB As New DataSet

    Private WithEvents bwExecuteNonQuery As System.ComponentModel.BackgroundWorker
    Private StartQuery As Date
    Private QuerySQL As String 'so completed event has access to SQL string
    Public IsQueryBusy As Boolean

    Private _IsLoggedIn As Boolean = False

    Public Property IsLoggedIn As Boolean
        Get
            Return _IsLoggedIn OrElse Password.Trim = ""
        End Get
        Set(ByVal value As Boolean)
            _IsLoggedIn = value
        End Set
    End Property

    ''' <summary>
    ''' If false, selected advanced menu and toolbar items will be hidden for non-Supervisor users
    ''' </summary>
    Public Property AdvancedMenusVisible As Boolean = True

    ''' <summary>
    ''' If true, non-Supervisors will have no ability to alter any Working tables in addition to other restrictions
    ''' </summary>
    Public Property IsReadOnly As Boolean = True

    ''' <summary>
    ''' If true, WQX fields and Support tables will be visible in the Support table browser
    ''' </summary>
    Public Property WQXVisible As Boolean = False

    Public WithEvents DB As clsDB

    ''' <summary>
    ''' Is raised during long operations (e.g., import/export) so caller can report using UI
    ''' </summary>
    ''' <param name="Value">Current value (must alway initialize with 0 and finalize with Value=MaxValue)</param>
    ''' <param name="MaxValue">Maximum value</param>
    ''' <param name="ProgressText">Text to display</param>
    Public Event Progress(ByRef DB As clsDB, ByVal Value As Integer, ByVal MaxValue As Integer, ByVal ProgressText As String)

#End Region

    ''' <summary>
    ''' List of standard WRDB tables associated with a project
    ''' </summary>
    Public Enum enumTableTypes As Integer
        Temporary
        Working  'may have many working tables
        Master   'single master table
        Stations 'following are support tables
        PCodes
        CCodes
        RCodes
        QCodes
        Branches
        StaGrp
        PCodeGrp
        ConvGrp
        Agencies 'validation table
        FIPSCodes 'validation table (will be automatically populated for entire U.S.)
        WQXMethods 'validation table (will be automatically populated with WQX items)
        Validation 'validation for station types, basin names, waterbodies, ecoregions, planregions, wqx characteristic names, sample fractions, and measure units (will be auto-populated for WQX items)
        Criteria  'criteria override table in which criteria limits in PCodes table can be overridden by a field in Stations table (used for advanced criteria analysis)
        Tracking
        MiniData 'special data table type containing only Station_ID, PCode, Date_Time, and Result used to create WASP input files
        Legacy   'WRDB 4.5 schema for working tables (Paradox exports use this)
        Other    'non-WRDB data tables that you want to hide so they are not opened as Working tables
    End Enum

    Public Enum enumDisplayRecordCount
        NotSet
        Hide
        Show
    End Enum

    Public Enum enumDesktopColorTheme
        None
        Water
        Desert
        Forest
    End Enum

    Public Enum enumQueryOption
        View
        Table
    End Enum

    ''' <summary>
    ''' Array of standard table names for each table type
    ''' </summary>
    Public _TableName() As String = {"Temporary", "Working", "Master", "Stations", "PCodes", "CCodes", "RCodes", "QCodes", "Branches", "StaGrp", "PCodeGrp", "ConvGrp", "Agencies", "FIPSCodes", "WQXMethods", "Validation", "Criteria", "Tracking", "MiniData", "Othr_", "Legacy"}

#Region "Properties"

    Public Property Name() As String

    Public Property Description() As String

    Public Property Password As String

    Public Property TitlebarText() As String

    Public Property DesktopGraphicFile() As String

    Public Property DesktopColorTheme() As enumDesktopColorTheme

    Public Property MasterTableName() As String

    Public Property WorkingDir() As String

    Public Property MasterDir() As String

    Public Property SupportDir() As String

    Public Property SupportDirProject() As String

    Public Property SQLDir() As String

    'this was removed 8/27/10 when last record counts placed in Tracking table
    'Public Property DisplayRecordCount() As enumDisplayRecordCount

    Public Property StoreObjects() As Boolean

    Private _QueryOption As enumQueryOption

    Public Property QueryOption() As enumQueryOption
        Get
            If DB IsNot Nothing AndAlso Not DB.SupportsViews Then Return enumQueryOption.Table Else Return _QueryOption
        End Get
        Set(ByVal value As enumQueryOption)
            _QueryOption = IIf(DB IsNot Nothing AndAlso Not DB.SupportsViews, enumQueryOption.Table, value)
        End Set
    End Property

    Public Property RptDateTimeRCode() As Boolean

    ''' <summary>
    ''' Flag for sort by date-time report; if true will consolidate all readings during a date (ignoring time)
    ''' </summary>
    Public Property RptDateTimeDate() As Boolean

    Public Property GisQuickList() As Boolean

    Public Property GisQuickTable() As Boolean

    Public Property GisDirectory() As String

    Public Property GisBasemapStations As String

    Public Property GisBasemapOutput As String

#End Region

    Public Function TableName(ByVal Tabletype As enumTableTypes) As String
        Return _TableName(Tabletype)
    End Function

    ''' <summary>
    ''' Alternative way of initializing because can't include "New" entries in interface used by EPDRiv1
    ''' </summary>
    Public Sub New()
        Initialize("")
    End Sub

    ''' <summary>
    ''' Get preferences and setup info from INI file for the project (or assign defaults)
    ''' </summary>
    ''' <param name="ProjectName">Name of project from WRDB.INI file</param>
    Public Sub New(ByVal ProjectName As String, Optional ByVal IsTempProject As Boolean = False, Optional ByVal IniFileName As String = "")
        Initialize(ProjectName, IsTempProject, IniFileName)
    End Sub

    ''' <summary>
    ''' Alternative way of initializing because can't include "New" entries in interface used by EPDRiv1
    ''' </summary>
    ''' <param name="ProjectName">Name of project from WRDB.INI file</param>
    ''' <param name="IsTempProject">If true, is a temporary project which should not already exist in INI file (bypass reading which is slow)</param>
    ''' <remarks>This also establishes all standard table structures and stored them in empty datatables within a global dataset; it allows tables to be created in provider-independent manner</remarks>
    Public Sub Initialize(ByVal ProjectName As String, Optional ByVal IsTempProject As Boolean = False, Optional ByVal IniFileName As String = "")

        If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)

        Me.Name = Strings.Left(ProjectName, 30)

        'DLLPath = IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly.Location)

        ' Configuration file in "ini" format containing general and project-specific settings (note: references current global Project variable)
        ' Ini file used for all program settings (except form size and position) instead of My.Settings because latter cannot handle variable number of projects
        ' The IniFile variable is instantiated when clsWRDB is initialized; the file is WRDB.ini and is stored in AppDataPath folder
        ' (e.g., C:\Documents and Settings\LCW\My Documents\WRDB\WRDB.INI)

        If Not IsTempProject Then
            Dim IniFile As New clsWRDBIniFile(IniFileName)
            With IniFile
                Me.Description = .GetKey(ProjectName, clsWRDBIniFile.enumKey.Description)
                Me.Password = .GetKey(ProjectName, clsWRDBIniFile.enumKey.Password, "Not Set") 'if older project with no entry, this will allow user to be warned later (but see below; if no database password, will reset to "")
                If Me.Password <> "Not Set" Then
                    Try
                        Me.Password = DecryptText(Me.Password)
                        If Me.Password = "Blank" Then Me.Password = ""
                    Catch ex As Exception
                        Me.Password = ""
                    End Try
                End If
                Me.TitlebarText = .GetKey(ProjectName, clsWRDBIniFile.enumKey.TitleBarText)
                Me.DesktopGraphicFile = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.DesktopGraphic))
                Me.DesktopColorTheme = System.Enum.Parse(GetType(enumDesktopColorTheme), (.GetKey(ProjectName, clsWRDBIniFile.enumKey.DesktopColorTheme, "None")))
                Me.WorkingDir = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.Path_Working))
                Me.MasterDir = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.Path_Master))
                Me.SupportDir = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.Path_Support))
                Me.SupportDirProject = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.Path_SupportProj))
                Me.MasterTableName = .GetKey(ProjectName, clsWRDBIniFile.enumKey.MasterTable)
                'Me.DisplayRecordCount = .GetKey(ProjectName, clsWRDBIniFile.enumKey.DisplayRecordCount, CStr(enumDisplayRecordCount.NotSet))
                Me.QueryOption = .GetKey(ProjectName, clsWRDBIniFile.enumKey.QueryOption, "0")
                Me.AdvancedMenusVisible = .GetKey(ProjectName, clsWRDBIniFile.enumKey.AdvancedMenus, "False")
                Me.IsReadOnly = .GetKey(ProjectName, clsWRDBIniFile.enumKey.IsReadOnly, "True")
                Me.WQXVisible = .GetKey(ProjectName, clsWRDBIniFile.enumKey.WQXVisible, "False")
                Me.RptDateTimeRCode = .GetKey(ProjectName, clsWRDBIniFile.enumKey.Rpt_DateTimeRCode, "False")
                Me.RptDateTimeDate = .GetKey(ProjectName, clsWRDBIniFile.enumKey.Rpt_DateTimeDate, "False")
                Me.GisQuickList = .GetKey(ProjectName, clsWRDBIniFile.enumKey.GisQuickList, "False")
                Me.GisQuickTable = .GetKey(ProjectName, clsWRDBIniFile.enumKey.GisQuickTable, "False")
                Me.GisDirectory = .GetKey(ProjectName, clsWRDBIniFile.enumKey.GisDirectory, "")
                Me.GisBasemapStations = .GetKey(ProjectName, clsWRDBIniFile.enumKey.GisBasemapStations, "")
                Me.GisBasemapOutput = .GetKey(ProjectName, clsWRDBIniFile.enumKey.GisBasemapOutput, "")

                Dim dbTypeString As String = .GetKey(ProjectName, clsWRDBIniFile.enumKey.DatabaseType, "")
                If dbTypeString <> "" Then
                    Dim dbType As clsDB.enumDBType = System.Enum.Parse(GetType(clsDB.enumDBType), dbTypeString)
                    Try
                        Me.DB = clsDB.GetDataProvider(dbType)
                    Catch ex As DbSqlException
                        Throw
                        Exit Sub
                    End Try
                    Me.DB.DatabaseType = System.Enum.Parse(GetType(clsDB.enumDBType), .GetKey(ProjectName, clsWRDBIniFile.enumKey.DatabaseType, clsDB.enumDBType.SQLite.ToString))
                    Me.DB.DataSource = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.DataSource)) 'server name or filename

                    'if is filename and is not rooted, assume is relative to directory containing WRDB.ini file
                    Select Case DB.DatabaseType
                        Case clsDB.enumDBType.Access, clsDB.enumDBType.Firebird_Embedded, clsDB.enumDBType.Paradox, clsDB.enumDBType.SQL_Server_CE, clsDB.enumDBType.SQLite
                            If Not IO.Path.IsPathRooted(Me.DB.DataSource) Then Me.DB.DataSource = IO.Path.GetDirectoryName(IniFileName) & "\" & Me.DB.DataSource
                    End Select

                    Me.DB.DatabaseName = .GetKey(ProjectName, clsWRDBIniFile.enumKey.DatabaseName)
                    Me.DB.UserName = .GetKey(ProjectName, clsWRDBIniFile.enumKey.DbUserName)
                    Try
                        Me.DB.Password = DecryptText(.GetKey(ProjectName, clsWRDBIniFile.enumKey.DbPassword))
                    Catch ex As Exception
                        'Throw new applicationexception("Unable to decrypt password; either the password has changed or the WRDB.INI file has been manually edited. Please edit this Project's settings to respecify the database password.")
                        'Exit Sub
                        Me.DB.Password = ""
                    End Try
                    If Me.DB.Password = "" And Me.Password = "Not Set" Then Me.Password = "" 'if old project and no database password, assume no desire to password project project
                    Me.DB.CommandTimeout = .GetKey(ProjectName, clsWRDBIniFile.enumKey.Timeout, "30")
                    Try
                        Me.DB.CreateConnection()
                    Catch ex As IO.FileNotFoundException
                        Throw
                        Exit Sub
                    Catch ex As Exception
                        Throw New DbSqlException(String.Format("Support libraries and/or support files for the requested data provider ({0}) could not be found on the requested path ({1}) which prevented a successful connection; please select a different provider or place the required libraries and support file in the specified directory. You can download data provider libraries from the WRDB web site.", Me.DB.DatabaseType.ToString, IO.Path.GetDirectoryName(Reflection.Assembly.GetExecutingAssembly.Location).Replace("\", "\\")))
                        Exit Sub
                    End Try
                    Me.SQLDir = ReplaceMyDocs(.GetKey(ProjectName, clsWRDBIniFile.enumKey.Path_SQL, ""))
                    If SQLDir = "" And DB.DataSource <> "" Then
                        Select Case DB.DatabaseType
                            Case clsDB.enumDBType.Paradox : SQLDir = WorkingDir
                            Case clsDB.enumDBType.Access, clsDB.enumDBType.Firebird_Embedded, clsDB.enumDBType.SQLite : SQLDir = IO.Path.GetDirectoryName(DB.DataSource)
                        End Select
                    End If
                    Dim IsPDox As Boolean = (DB.DatabaseType = clsDB.enumDBType.Paradox)
                End If
            End With

            IniFile = Nothing
        End If

        'paradox temporary tables are not indexed (so can write to them using modParadox), but working and master are
        'RecID is sequential number; cannot use autoincrement because not all providers allow it; handle manually
        'TODO: WRDB 4.5 gives an error message if you open this table (it wants to see a primary key field called INDEX--what a horrible choice)
        'To allow backwards compatibility, distribute minor fix to WRDBLib that also allows RecID

        Dim dt As DataTable

        dsWRDB.Tables.Clear()

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Temporary))
            With .Columns
                'no longer using RecID to create primary index; instead just use all other fields
                'note that this means that duplicate records cannot be stored
                'If DB IsNot Nothing AndAlso DB.IsPrimaryKeyRequired Then .Add(NewColumn("RecID", "RecID", GetType(Integer)))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
                .Add(NewColumn("LEW_Pct", "LEW", GetType(Double)))
                .Add(NewColumn("Depth_M", "Depth", GetType(Double)))
                .Add(NewColumn("Agency", "Acy", GetType(String), 5))
                .Add(NewColumn("CCode", "CCode", GetType(String), 3))
                .Add(NewColumn("LCode", "L", GetType(String), 3))
                .Add(NewColumn("SCode", "S", GetType(String), 3))
                .Add(NewColumn("QCode", "Q", GetType(String), 1))
                .Add(NewColumn("RCode", "R", GetType(String), 1))
                .Add(NewColumn("Result", "Result", GetType(Double)))
                .Add(NewColumn("Validated", "Val", GetType(Boolean), , False))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
                .Add(NewColumn("Track_ID", "Trk ID", GetType(Integer)))
                If _StoreObjects Then
                    .Add(NewColumn("Object_Type", "Attach", GetType(String), 5)) 'string describing type of data stored (often file extension)
                    .Add(NewColumn("Object_Value", "Item Val", GetType(Byte()))) 'binary data
                End If
            End With
            'data tables may have primary key (these will not be used unless DB.CreateTable is passed MustBeUpdateable as true)
            'note that some providers required the fields be non-nullable, so don't include Agency field
            '.PrimaryKey = New DataColumn() {.Columns(0), .Columns(1), .Columns(2), .Columns(3), .Columns(4), .Columns(6), .Columns(7), .Columns(8)}
            dt = .Copy
            dt.TableName = TableName(enumTableTypes.Working)
            dsWRDB.Tables.Add(dt)
            dt = dt.Copy
            dt.TableName = TableName(enumTableTypes.Master)
            dsWRDB.Tables.Add(dt)
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Stations))
            With .Columns
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
                .Add(NewColumn("Station_Name", "Station Name", GetType(String), 60))
                .Add(NewColumn("Station_Type", "Station Type", GetType(String), 60))
                .Add(NewColumn("Data_Freq", "Data Freq", GetType(String), 8))
                .Add(NewColumn("Latitude", "Latitude", GetType(Double)))
                .Add(NewColumn("Longitude", "Longitude", GetType(Double)))
                .Add(NewColumn("Elevation", "Elevation", GetType(Double)))
                .Add(NewColumn("HUC", "HUC", GetType(String), 14))
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("River_Mile", "River Mile", GetType(Double)))
                .Add(NewColumn("Reach_ID", "Reach ID", GetType(String), 25))
                .Add(NewColumn("Dr_Area", "Dr Area", GetType(Double)))
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Ref_ID", "Reference ID", GetType(String), 20))
                .Add(NewColumn("Remarks", "Remarks", GetType(String), 50))
                .Add(NewColumn("Other", "Other", GetType(String), 50))
                .Add(NewColumn("WaterBodyType", "Water Body Type", GetType(String), 30))
                .Add(NewColumn("Basin_Name", "Basin Name", GetType(String), 30))
                .Add(NewColumn("State", "State", GetType(String), 2))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("FIPS_Code", "FIPS Code", GetType(Integer)))
                .Add(NewColumn("EcoRegion", "Eco Region", GetType(String), 30))
                .Add(NewColumn("PlanRegion", "Water Plan Region", GetType(String), 30))
                .Add(NewColumn("Convert_Station_ID", "Convert To", GetType(String), 25))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.PCodes))
            With .Columns
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
                .Add(NewColumn("Anal_Name", "Analysis Name", GetType(String), 60))
                .Add(NewColumn("Units", "Units", GetType(String), 60))
                .Add(NewColumn("Test_Method", "Test Method", GetType(String), 12))
                .Add(NewColumn("Det_Limit", "Det. Limit", GetType(Double), , 0))
                .Add(NewColumn("Sig_Figs", "Sig Figs", GetType(Double), , 2))
                .Add(NewColumn("Min_Limit", "Min. Limit", GetType(Double)))
                .Add(NewColumn("Max_Limit", "Max. Limit", GetType(Double)))
                .Add(NewColumn("Min_Criteria", "Min. Crit.", GetType(Double)))
                .Add(NewColumn("Max_Criteria", "Max. Crit.", GetType(Double)))
                .Add(NewColumn("Format_Str", "Fmt Str", GetType(String), 10, "w.2"))
                .Add(NewColumn("Convert_PCode", "Convert To", GetType(String), 10))
                .Add(NewColumn("Convert_Units", "Units", GetType(String), 12, "mg/L"))
                .Add(NewColumn("Convert_Mult", "Multiplier", GetType(Double), , 1.0))
                .Add(NewColumn("WQX_CharacteristicName", "WQX Characteristic Name", GetType(String), 120))
                .Add(NewColumn("WQX_ResultSampFraction", "WQX Sample Fraction", GetType(String), 25))
                .Add(NewColumn("WQX_ResultMeasureUnit", "WQX Units", GetType(String), 12))
                .Add(NewColumn("WQX_AnalyticalMethodIDContext", "WQX Method ID Context", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodID", "WQX Method ID", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodName", "WQX Method Name", GetType(String), 120))
                .Add(NewColumn("WQX_AnalyticalMethodQualifType", "WQX Method Type", GetType(String), 25))
                .Add(NewColumn("WQX_AnalyticalMethodDesc", "WQX Method Desc", GetType(String), 500))
                .Add(NewColumn("Create_Update", "Updated", GetType(Date)))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.CCodes))
            With .Columns
                .Add(NewColumn("CCode", "CCode", GetType(String), 3))
                .Add(NewColumn("CCode_Name", "Name", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 30))
                .Add(NewColumn("Convert_CCode", "Convert To", GetType(String), 3))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.RCodes))
            With .Columns
                .Add(NewColumn("RCode", "RCode", GetType(String), 1))
                .Add(NewColumn("Description", "Description", GetType(String), 250))
                .Add(NewColumn("Convert_RCode", "Convert To", GetType(String), 1))
                .Add(NewColumn("Calc_Stats", "Calc Stats?", GetType(Boolean), , True))
                .Add(NewColumn("NonDetect_PCode", "Is Non-Detect (Use PCode Limit)", GetType(Boolean), , True))
                .Add(NewColumn("NonDetect_Result", "Is Non-Detect (Use Result Limit)", GetType(Boolean), , False))
                .Add(NewColumn("Detect_Mult", "Limit Multiplier", GetType(Double), , 0.5))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.QCodes))
            With .Columns
                .Add(NewColumn("QCode", "QCode", GetType(String), 1))
                .Add(NewColumn("QCode_Name", "Name", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 30))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Branches))
            With .Columns
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("Branch_Name", "Name", GetType(String), 60))
                .Add(NewColumn("DS_Branch_ID", "DS Branch ID", GetType(String), 16))
                .Add(NewColumn("DS_Branch_RM", "DS RMile", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.StaGrp))
            With .Columns
                .Add(NewColumn("StationGroup", "Group Name", GetType(String), 100))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.PCodeGrp))
            With .Columns
                .Add(NewColumn("PCodeGroup", "Group Name", GetType(String), 15))
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.ConvGrp))
            With .Columns
                .Add(NewColumn("ConvGroup", "Group Name", GetType(String), 15))
                .Add(NewColumn("ConvFrom", "Convert From", GetType(String), 250))
                .Add(NewColumn("ConvTo", "Convert To", GetType(String), 250))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1), .Columns(2)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Agencies))
            With .Columns
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Agency_Name", "Name", GetType(String), 100))
                .Add(NewColumn("Description", "Description", GetType(String), 100))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.FIPSCodes))
            With .Columns
                .Add(NewColumn("FIPS_Code", "FIPS Code", GetType(Integer)))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("State", "State", GetType(String), 2))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.WQXMethods))
            With .Columns
                .Add(NewColumn("WQX_AnalyticalMethodIDContext", "WQX Method ID Context", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodID", "WQX Method ID", GetType(String), 20))
                .Add(NewColumn("WQX_AnalyticalMethodName", "WQX Method Name", GetType(String), 120))
                .Add(NewColumn("WQX_AnalyticalMethodQualifType", "WQX Method Type", GetType(String), 25))
                .Add(NewColumn("WQX_AnalyticalMethodDesc", "WQX Method Desc", GetType(String), 500))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Validation))
            With .Columns
                .Add(NewColumn("Field_Name", "Field Name", GetType(String), 40))
                .Add(NewColumn("Valid_Value", "Valid Value", GetType(String), 120))
                .Add(NewColumn("Description", "Description", GetType(String), 120))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Criteria))
            With .Columns
                .Add(NewColumn("CritGroup", "Group Name", GetType(String), 15))
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
                .Add(NewColumn("Month_List", "Month List", GetType(String), 50))
                .Add(NewColumn("Priority", "Priority Mult", GetType(Double)))
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
                .Add(NewColumn("Station_Type", "Station Type", GetType(String), 60))
                .Add(NewColumn("HUC", "HUC", GetType(String), 14))
                .Add(NewColumn("Branch_ID", "Branch ID", GetType(String), 16))
                .Add(NewColumn("Reach_ID", "Reach ID", GetType(String), 25))
                .Add(NewColumn("Agency", "Agency", GetType(String), 5))
                .Add(NewColumn("Remarks", "Remarks", GetType(String), 50))
                .Add(NewColumn("Other", "Other", GetType(String), 50))
                .Add(NewColumn("WaterBodyType", "Water Body Type", GetType(String), 30))
                .Add(NewColumn("Basin_Name", "Basin Name", GetType(String), 30))
                .Add(NewColumn("County_Name", "County Name", GetType(String), 50))
                .Add(NewColumn("EcoRegion", "Eco Region", GetType(String), 30))
                .Add(NewColumn("PlanRegion", "Water Plan Region", GetType(String), 30))
                .Add(NewColumn("Min_Criteria", "Min. Crit.", GetType(Double)))
                .Add(NewColumn("Max_Criteria", "Max. Crit.", GetType(Double)))
                .Add(NewColumn("Violation_Type", "Violation Type", GetType(String), 10))
                .Add(NewColumn("Violation_Freq", "Frequency", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1), .Columns(2)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.Tracking))
            With .Columns
                .Add(NewColumn("Project_Name", "Project Name", GetType(String), 30))
                .Add(NewColumn("Temp_DB_Name", "Working Table", GetType(String), 100))
                .Add(NewColumn("Master_DB_Name", "Master/Import Tbl", GetType(String), 100))
                .Add(NewColumn("TableGroup", "Table Group", GetType(String), 30))
                .Add(NewColumn("Description", "Description", GetType(String), 100))
                .Add(NewColumn("Source", "Data Source", GetType(String), 100))
                .Add(NewColumn("QCode", "QCode", GetType(String), 1))
                .Add(NewColumn("Q_Info", "Quality Information", GetType(String), 100))
                .Add(NewColumn("Create_Date", "Creation Date", GetType(Date)))
                .Add(NewColumn("Create_Init", "By", GetType(String), 30))
                .Add(NewColumn("Validate_Date", "Validation Date", GetType(Date)))
                .Add(NewColumn("Validate_Init", "By", GetType(String), 30))
                .Add(NewColumn("Transfer_Date", "Xfer/Import Date", GetType(Date)))
                .Add(NewColumn("Transfer_Init", "By", GetType(String), 30))
                .Add(NewColumn("Record_Count", "Record Count", GetType(Integer)))
                .Add(NewColumn("Create_Update", "Last Updated", GetType(Date)))
                .Add(NewColumn("Track_ID", "Tracking ID", GetType(Integer)))
                .Add(NewColumn("Is_Import", "Is Import?", GetType(Boolean), , False))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0), .Columns(1), .Columns(2)}
        End With

        With dsWRDB.Tables.Add(TableName(enumTableTypes.MiniData))
            With .Columns
                .Add(NewColumn("RecID", "RecID", GetType(Integer)))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("Result", "Result", GetType(Double)))
            End With
            .PrimaryKey = New DataColumn() {.Columns(0)}
        End With

        'compatible with WRDB 4.5 for Paradox
        With dsWRDB.Tables.Add(TableName(enumTableTypes.Legacy))
            With .Columns
                .Add(NewColumn("Station_ID", "Station ID", GetType(String), 25))
                .Add(NewColumn("Date_Time", "Date/Time", GetType(Date)))
                .Add(NewColumn("PCode", "PCode", GetType(String), 10))
                .Add(NewColumn("LEW_Pct", "LEW", GetType(Double)))
                .Add(NewColumn("Depth_M", "Depth", GetType(Double)))
                .Add(NewColumn("CCode", "CCode", GetType(String), 3))
                .Add(NewColumn("RCode", "R", GetType(String), 1))
                .Add(NewColumn("Result", "Result", GetType(Double)))
                .Add(NewColumn("Validated", "Val", GetType(Boolean), , False))
                .Add(NewColumn("Track_ID", "Trk ID", GetType(Integer)))
            End With
        End With

        If DB IsNot Nothing AndAlso DB.TestConnectionQuick(CONNECTIONTIMEOUT) Then
            TempPrefix = Me.DB.GetValidTableName(TempPrefix)
            DataPrefix = Me.DB.GetValidTableName(DataPrefix)
        End If
    End Sub

    ''' <summary>
    ''' Make deep copy of project
    ''' </summary>
    ''' <returns>New project</returns>
    Public Function Clone() As clsProject
        Dim NewProject As New clsProject(Name)
        With NewProject
            .Description = Description
            .DesktopGraphicFile = DesktopGraphicFile
            .DesktopColorTheme = DesktopColorTheme
            .MasterDir = MasterDir
            .MasterTableName = MasterTableName
            .SupportDir = SupportDir
            .SupportDirProject = SupportDirProject
            .TitlebarText = TitlebarText
            .WorkingDir = WorkingDir
            .IsLoggedIn = IsLoggedIn
            .Password = Password
            .AdvancedMenusVisible = AdvancedMenusVisible
            If DB IsNot Nothing Then .DB = DB.Clone
        End With
        Return NewProject
    End Function

    ''' <summary>
    ''' Set column caption headings and maximum column width using standard templates; usually called after AddTable
    ''' </summary>
    '<System.Diagnostics.DebuggerStepThrough()> _
    Public Sub SetTableHeadings(ByVal TableType As enumTableTypes)
        Dim ErrCol As String = ""
        Dim tbl As DataTable = DataSet.Tables(TableName(TableType))
        With GetTableTemplate(TableType)
            For Each col As DataColumn In .Columns
                For i As Integer = 0 To tbl.Columns.Count - 1
                    If tbl.Columns(i).ColumnName.Contains(col.ColumnName.ToUpper) Or tbl.Columns(i).ColumnName.Contains(col.ColumnName) Then
                        tbl.Columns(i).Caption = col.Caption
                        If tbl.Columns(i).DataType Is GetType(String) Then
                            Try
                                tbl.Columns(i).MaxLength = col.MaxLength
                            Catch ex As Exception
                                If DB.DatabaseType <> clsDB.enumDBType.Oracle Then ErrCol &= String.Format("\n{0} ({1})", col.Caption, col.MaxLength)
                            End Try
                        End If
                        Exit For
                    End If
                Next
            Next
            If ErrCol <> "" Then Throw New ApplicationException(String.Format("One or more entries in the following columns of the table '{0}' were had more than the maximum allowable number of characters\n{1}\n\nPlease edit the data to conform to the maximum limits.", .TableName, ErrCol))
        End With
    End Sub

    ''' <summary>
    ''' Set column caption headings and maximum column width using standard templates; usually called after AddTable
    ''' </summary>
    '<System.Diagnostics.DebuggerStepThrough()> _
    Public Sub SetTableHeadings(TableName As String)
        Dim ErrCol As String = ""
        Dim tbl As DataTable = DataSet.Tables(TableName)
        With GetTableTemplate(GetTableType(TableName))
            For Each col As DataColumn In .Columns
                For i As Integer = 0 To tbl.Columns.Count - 1
                    If tbl.Columns(i).ColumnName.Contains(col.ColumnName.ToUpper) Or tbl.Columns(i).ColumnName.Contains(col.ColumnName) Then
                        tbl.Columns(i).Caption = col.Caption
                        If tbl.Columns(i).DataType Is GetType(String) Then
                            Try
                                tbl.Columns(i).MaxLength = col.MaxLength
                            Catch ex As Exception
                                If DB.DatabaseType <> clsDB.enumDBType.Oracle Then ErrCol &= String.Format("\n{0} ({1})", col.Caption, col.MaxLength)
                            End Try
                        End If
                        Exit For
                    End If
                Next
            Next
            If ErrCol <> "" Then Throw New ApplicationException(String.Format("One or more entries in the following columns of the table '{0}' were had more than the maximum allowable number of characters\n{1}\n\nPlease edit the data to conform to the maximum limits.", .TableName, ErrCol))
        End With
    End Sub

    ''' <summary>
    ''' Save all preferences to this project's INI file
    ''' </summary>
    Public Sub SavePreferences()
        Dim IniFile As New clsWRDBIniFile
        With IniFile
            .SetProjectName(Name)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Description, Description)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Password, EncryptText(IIf(Password = "", "Blank", Password)))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.TitleBarText, TitlebarText)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DesktopGraphic, ReplaceMyDocs(DesktopGraphicFile))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DesktopColorTheme, DesktopColorTheme.ToString)
            '.SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.AppExpertMode, WRDB.ExpertMode.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Path_Working, ReplaceMyDocs(WorkingDir))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Path_Master, ReplaceMyDocs(MasterDir))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Path_Support, ReplaceMyDocs(SupportDir))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Path_SupportProj, ReplaceMyDocs(SupportDirProject))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.MasterTable, MasterTableName)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DatabaseType, DB.DatabaseType.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DataSource, ReplaceMyDocs(DB.DataSource))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DatabaseName, DB.DatabaseName)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DbUserName, DB.UserName)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DbPassword, EncryptText(DB.Password))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Timeout, DB.CommandTimeout.ToString)
            '.SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.DisplayRecordCount, CInt(DisplayRecordCount).ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.QueryOption, CInt(QueryOption).ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.AdvancedMenus, AdvancedMenusVisible.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.IsReadOnly, IsReadOnly.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.WQXVisible, WQXVisible.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Path_SQL, ReplaceMyDocs(SQLDir))
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Rpt_DateTimeRCode, RptDateTimeRCode.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.Rpt_DateTimeDate, RptDateTimeDate.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.GisQuickList, GisQuickList.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.GisQuickTable, GisQuickTable.ToString)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.GisDirectory, GisDirectory)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.GisBasemapStations, GisBasemapStations)
            .SetKey(clsWRDBIniFile.enumSection.Project, clsWRDBIniFile.enumKey.GisBasemapOutput, GisBasemapOutput)
            .Save()
        End With
        IniFile = Nothing
    End Sub

    ''' <summary>
    ''' If path contains {MyDocs}, replace with full path; if starts with My Documents path, replace with {MyDocs}
    ''' </summary>
    ''' <param name="Path">Path to convert from</param>
    ''' <returns>Path with substitution made</returns>
    ''' <remarks></remarks>
    Shared Function ReplaceMyDocs(ByVal Path As String) As String
        If String.IsNullOrEmpty(Path) Then Return ""
        If Path.StartsWith("{MyDocs}", StringComparison.OrdinalIgnoreCase) Then
            Return Path.Replace("{MyDocs}", My.Computer.FileSystem.SpecialDirectories.MyDocuments)
        ElseIf Path.StartsWith(My.Computer.FileSystem.SpecialDirectories.MyDocuments, StringComparison.OrdinalIgnoreCase) Then
            Return Path.Replace(My.Computer.FileSystem.SpecialDirectories.MyDocuments, "{MyDocs}")
        Else
            Return Path
        End If
    End Function

    'Private Function GetField(ByVal dr As DbDataReader, ByVal FieldName As String) As String

    '    Dim fieldnum As Int16 = dr.GetOrdinal(FieldName)
    '    If dr.IsDBNull(fieldnum) Then Return "NULL"
    '    Dim fieldtype As System.Type = dr.GetFieldType(fieldnum)
    '    If fieldtype Is GetType(DateTime) Then
    '        Return "'" & dr.GetValue(fieldnum) & "'"
    '    ElseIf fieldtype Is GetType(String) Then
    '        Return "'" & dr.GetValue(fieldnum) & "'"
    '    ElseIf fieldtype Is GetType(Boolean) Then
    '        Return "'" & CType(dr.GetValue(fieldnum), String).Substring(0, 1) & "'"
    '    Else
    '        Return dr.GetValue(fieldnum)
    '    End If

    'End Function

    '''' <summary>
    '''' Start background query in a way that total duration can be measured
    '''' </summary>
    'Private Sub bwExecuteNonQuery_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bwExecuteNonQuery.DoWork
    '    QuerySQL = CType(e.Argument, String)
    '    StartQuery = Now
    '    IsQueryBusy = True
    '    e.Result = DB.ExecuteNonQuery(QuerySQL)
    'End Sub

    '''' <summary>
    '''' When query is complete, reports how many records were selected and how long it took
    '''' </summary>
    'Private Sub bwExecuteNonQuery_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bwExecuteNonQuery.RunWorkerCompleted
    '    WriteProgress(True)
    '    If e.Result = -2 Then
    '        ErrorMsg("Unable to execute SQL string.", Me.QuerySQL, e.Error)
    '    Else
    '        Dim s As String = StringFormat("{0} of {1} records were selected ({2:0}%)\n\nThe query took {3:0.00} seconds to execute.", e.Result, ActiveBrowser.MyTotalRecords, e.Result * 100.0 / ActiveBrowser.MyTotalRecords, (Now.ToOADate - StartQuery.ToOADate) * 24 * 60 * 60)
    '        If ExpertMode Then
    '            WriteStatus(s.Replace(vbCr & vbCr, "; "), 5)
    '        Else
    '            MessageBox.Show(s, "WRDB Query", MessageBoxButtons.OK, MessageBoxIcon.Information)
    '        End If
    '    End If
    '    IsQueryBusy = False
    '    bwExecuteNonQuery.Dispose()
    '    bwExecuteNonQuery = Nothing
    'End Sub

    ''' <summary>
    ''' Helper method to fill combo- or listbox controls with a list of keyfields, hyphen, description (key - descr)
    ''' List will be sorted and set to first value in the list (or default value is it exists)
    ''' To retrieve the keyfield value, use GetPickList function
    ''' (This version is intended for use with Validation Support table)
    ''' </summary>
    ''' <param name="List_ComboBox">Listbox or combobox object, or Generic list of string</param>
    ''' <param name="ValidationKey">Name of validation key (e.g., "SCODE")</param>
    ''' <param name="AdditionalItem">Optional additional list item (e.g., "(Select from list)"</param>
    ''' <param name="DefaultKey">Default key value, if exists in list (if blank, will be set to first item in list)</param>
    Public Sub FillPickListValidation(ByRef List_ComboBox As Object, ByVal ValidationKey As String, Optional ByVal AdditionalItem As String = "", Optional ByVal DefaultKey As String = "")

        Dim dr As DbDataReader = Nothing

        Try
            Dim DefaultText As String = ""
            Dim List As New Generic.List(Of String)

            With List
                .Clear()
                If AdditionalItem <> "" Then .Add(AdditionalItem)
                dr = DB.ExecuteReader("SELECT DISTINCT Valid_Value,Description FROM Validation WHERE Field_Name='{0}' ORDER BY Valid_Value", ValidationKey.ToUpper)
                While dr IsNot Nothing AndAlso dr.Read
                    If Not dr.IsDBNull(0) Then
                        .Add(dr.GetString(0) & " - " & TestNull(dr.GetString(1), ""))
                    End If
                End While
                dr.Close()
            End With

            If TypeOf List_ComboBox Is Generic.List(Of String) Then
                List_ComboBox = List
            Else 'is list or combobox
                With List_ComboBox
                    .items.clear()
                    For Each s As String In List
                        .items.add(s)
                    Next
                    If DefaultText <> "" Then
                        .Text = DefaultText
                    Else
                        .SelectedIndex = IIf(.Items.Count = 0, -1, 0)
                    End If
                End With
            End If
        Catch ex As Exception
            Throw
        Finally
            If dr IsNot Nothing Then dr.Close()
            DB.CloseConnection()
        End Try
    End Sub

    ''' <summary>
    ''' Return list of tablenames that match list of table types (or Working if empty list) (converted to Proper case)
    ''' </summary>
    ''' <param name="TableType">List of table types (if empty, returns list of working tables); use type enumTableTypes</param>
    ''' <returns>List of table names</returns>
    Public Function GetTableList(ByVal ParamArray TableType() As enumTableTypes) As Generic.List(Of String)
        Dim lst As New Generic.List(Of String)
        If TableType.Length = 0 Then TableType = New enumTableTypes() {enumTableTypes.Working}
        For Each tt As enumTableTypes In TableType
            ResetPDox(tt)
            Dim tblList As Generic.List(Of String) = DB.GetTableList
            If tblList Is Nothing Then Return Nothing
            For Each tbl As String In tblList
                If TableType.Length = 0 Then
                    If GetTableType(tbl) = enumTableTypes.Working And Not lst.Contains(tbl) Then lst.Add(tbl)
                Else
                    If GetTableType(tbl) = tt And Not lst.Contains(tbl) Then lst.Add(tbl)
                End If
            Next
        Next
        Return lst
    End Function

    ''' <summary>
    ''' Return list of all data and support tablenames; only working tables assigned to this project will be included 
    ''' </summary>
    ''' <returns>List of table names</returns>
    Public Function GetTableListAll() As Generic.List(Of String)
        If Not ResetPDox("Tracking") Then Return Nothing
        'list will contain all table names assigned to a different project
        Dim ExcludeList As New Generic.List(Of String)
        If DB.GetColumnList("Tracking").Contains("PROJECT_NAME") Then
            Dim dr As DbDataReader = DB.ExecuteReader("SELECT DISTINCT Temp_DB_Name,Project_Name FROM Tracking WHERE {0}(Project_Name)<>'{1}'", DB.UCase, Name.ToUpper)
            While dr IsNot Nothing AndAlso dr.Read
                ExcludeList.Add(dr.GetString(0).ToUpper)
            End While
            dr.Close()
            dr = Nothing
        End If

        Dim lst As New Generic.List(Of String)

        If Not ResetPDox() Then Return Nothing

        For Each tbl As String In DB.GetTableList()
            If GetTableType(tbl) = enumTableTypes.Working And Not ExcludeList.Contains(tbl.ToUpper) Then lst.Add(tbl)
        Next

        For tt As enumTableTypes = enumTableTypes.Master To enumTableTypes.Tracking
            ResetPDox(tt)
            For Each tbl As String In DB.GetTableList
                If GetTableType(tbl) = tt And Not lst.Contains(tbl) Then lst.Add(tbl)
            Next
        Next
        Return lst
    End Function

    ''' <summary>
    ''' Given the table name, return which type of table it is
    ''' </summary>
    ''' <param name="_TableName">Standard table name (no brackets, etc.)</param>
    Public Function GetTableType(_TableName As String) As clsProject.enumTableTypes
        _TableName = _TableName.Replace("[", "").Replace("]", "")

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            (_TableName.StartsWith(clsProject.TempPrefix, StringComparison.OrdinalIgnoreCase) Or
             _TableName.StartsWith("TEMP_", StringComparison.OrdinalIgnoreCase)) Then Return enumTableTypes.Temporary

        If Not String.IsNullOrEmpty(_TableName) AndAlso
            _TableName.StartsWith(clsProject.OtherPrefix, StringComparison.OrdinalIgnoreCase) Then Return enumTableTypes.Other

        For i As enumTableTypes = enumTableTypes.Stations To enumTableTypes.Tracking
            If _TableName.Equals(TableName(i), StringComparison.OrdinalIgnoreCase) Then Return i
        Next

        If _TableName.Trim = "" Then Return enumTableTypes.Working

        If _TableName.Equals(MasterTableName, StringComparison.OrdinalIgnoreCase) Then Return enumTableTypes.Master

        If DB.DatabaseType = clsDB.enumDBType.Paradox AndAlso Not WorkingDir.Equals(MasterDir, StringComparison.OrdinalIgnoreCase) Then 'see if table is in master dir
            If My.Computer.FileSystem.FileExists(MasterDir & _TableName & ".db") Then Return enumTableTypes.Master
        End If
        Return enumTableTypes.Working
    End Function

    ''' <summary>
    ''' Is specified table a support table?
    ''' </summary>
    ''' <param name="_TableName">Name of table (case ignored)</param>
    ''' <returns>True if is Support table</returns>
    Public Function IsSupportTable(_TableName As String) As Boolean
        Select Case GetTableType(_TableName)
            Case enumTableTypes.Stations To enumTableTypes.Tracking
                Return True
            Case Else
                Return False
        End Select
    End Function

    ''' <summary>
    ''' Get the table structure based on the table name
    ''' </summary>
    ''' <param name="TableName">Name of table</param>
    ''' <returns>Data table structured like desired table</returns>
    Public Function GetTableTemplate(ByVal TableName As String) As DataTable
        If GetTableType(TableName) = enumTableTypes.Other Then Return Nothing Else Return dsWRDB.Tables(GetTableType(TableName))
    End Function

    ''' <summary>
    ''' Get the table structure based on the table type
    ''' </summary>
    ''' <param name="TableType">Type of table</param>
    ''' <returns>Data table structured like desired table</returns>
    Public Function GetTableTemplate(ByVal TableType As enumTableTypes) As DataTable
        Return dsWRDB.Tables(TableType)
    End Function

    ''' <summary>
    ''' Copy and rename table in current database, or copy from another project
    ''' </summary>
    ''' <param name="DestTable">Name of destination table</param>
    ''' <param name="SourceTable">Name of source table</param>
    ''' <param name="SourceProject">Source project (if Nothing, assumes current project, and cannot update)</param>
    ''' <param name="Update">True if destination table should be updated (requires slower export/import even if in same database)</param>
    ''' <returns>True if successful</returns>
    Public Function CopyTable(ByRef DestTable As String, ByVal SourceTable As String, Optional ByRef SourceProject As clsProject = Nothing, Optional ByVal Update As Boolean = False, Optional ByVal OverwriteIfExists As Boolean = True, Optional ByVal AutoRename As Boolean = False) As Boolean

        DestTable = DB.GetValidTableName(DestTable)
        If DestTable = "" Then Return False
        Dim TableType As enumTableTypes = GetTableType(SourceTable)

        If SourceProject Is Nothing And Not Update Then 'copy table within the same project--must have unique tablename and destination must be empty
            If DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    DeleteTable(DestTable)
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            ResetPDox(SourceTable)
            If Not CreateTable(TableType, DestTable, TableType <> enumTableTypes.Working And DB.DatabaseType = clsDB.enumDBType.Paradox) Then Return False
            UpdateTrackingRecord(DestTable, , True)
            Return DB.AppendTable(DestTable, SourceTable)
        Else 'copy tables across projects (or do append/update of existing table)--do export then import
            If Not My.Computer.FileSystem.DirectoryExists(TempPath) Then My.Computer.FileSystem.CreateDirectory(TempPath)
            Dim TextFile As String = String.Format("{0}\{1}.txt", TempPath, SourceTable)
            If SourceProject Is Nothing Then SourceProject = Me
            If Not SourceProject.ExportTable(SourceTable, TextFile) Then Return False
            If Not Update AndAlso DB.TableExists(DestTable) Then
                If OverwriteIfExists Then
                    DeleteTable(DestTable)
                Else
                    Do
                        DestTable = "Copy_of_" & DestTable
                    Loop Until Not DB.TableExists(DestTable)
                End If
            End If
            If Not DB.TableExists(DestTable) AndAlso Not CreateTable(TableType, DestTable, (TableType <> enumTableTypes.Working And TableType <> enumTableTypes.Temporary) Or DB.DatabaseType = clsDB.enumDBType.Paradox) Then Return False
            Return ImportTable(DestTable, TextFile, Update)
        End If
    End Function

    ''' <summary>
    ''' Import data from tab-delimited file into table; if table exists, will be emptied first (or updated), if not will be created
    ''' Paradox data tables will be made updateable if they already contain records
    ''' The file format is assumed to be identical to the export format
    ''' </summary>
    ''' <param name="TableName">New table to import data into</param>
    ''' <param name="ImportFileName">Tab-delimited text file to import data from</param>
    ''' <param name="Append_Update">If false, existing table will be emptied first; otherwise will be appended to or replaced on record-by-record basis (if indexed)</param>
    ''' <param name="AppendOnly">If Append_Update is true and AppendOnly is true, will not replace existing records with new records</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Override for Paradox</remarks>
    Public Function ImportTable(ByRef TableName As String, ByVal ImportFileName As String, Optional ByVal Append_Update As Boolean = False, Optional ByVal AppendOnly As Boolean = False) As Boolean
        If Not ResetPDox(TableName) Then Return False
        Dim MustBeUpdateable As Boolean = False
        If GetTableType(TableName) <= enumTableTypes.Master Then
            If DB.IsPrimaryKeyRequired() AndAlso Append_Update AndAlso DB.TableExists(TableName) AndAlso DB.GetRecordCount(TableName) > 0 Then MustBeUpdateable = True
        Else
            MustBeUpdateable = True
        End If
        If DB.ImportTable(TableName, ImportFileName, GetTableTemplate(TableName), Append_Update, AppendOnly, MustBeUpdateable) Then
            If GetTableType(TableName) = enumTableTypes.Working Then UpdateTrackingRecord(TableName, DB.GetRecordCount(TableName), True)
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Export table to tab-delimited file (wrapper for clsGenericData that resets paradox connection first)
    ''' </summary>
    ''' <param name="TableName">Name of existing table</param>
    ''' <param name="ExportFileName">Name of file to export to (if blank, will export to temp file and return name here; caller must place in clipboard and delete tempfile)</param>
    ''' <param name="SQLString">If provided, uses SQL statement to select data to be exported</param>
    ''' <returns>True if successful</returns>
    Public Function ExportTable(ByRef TableName As String, ByRef ExportFileName As String, Optional ByVal SQLString As String = "") As Boolean
        If Not ResetPDox(TableName) Then Return False
        Return DB.ExportTable(TableName, ExportFileName, SQLString)
    End Function

    ''' <summary>
    ''' Create an empty standard table (table type is taken from table name)
    ''' </summary>
    ''' <param name="TableName">The name of table to create</param>
    ''' <returns>True if successful</returns>
    Public Function CreateTable(ByRef TableName As String) As Boolean
        Return DB.CreateTable(TableName, GetTableTemplate(TableName), GetTableType(TableName) <> enumTableTypes.Working AndAlso DB.IsPrimaryKeyRequired)
    End Function

    ''' <summary>
    ''' Create an empty standard table
    ''' </summary>
    ''' <param name="TableType">The type of table to create</param>
    ''' <param name="NewTableName">If TableType is Working, must supply this table name; can optionally supply for other table types. If table needs to be renamed to conform to database rules, returns that new name.</param>
    ''' <param name="MustBeUpdateable">Set this to True only if creating data table and is Paradox, dBase, etc.</param>
    ''' <returns>True if successful</returns>
    ''' <remarks>Paradox table will be placed in their correct folders</remarks>
    Public Function CreateTable(ByVal TableType As enumTableTypes, Optional ByRef NewTableName As String = "", Optional ByVal MustBeUpdateable As Boolean = True) As Boolean

        Dim TblName As String
        Select Case TableType
            Case enumTableTypes.Working
                TblName = DB.GetValidTableName(NewTableName)
                If TblName = "" Then Return False
            Case enumTableTypes.Master
                If NewTableName <> "" Then 'sometimes need to create a temp table in the master folder
                    TblName = NewTableName
                Else
                    TblName = MasterTableName
                End If
            Case Else
                If NewTableName = "" Then TblName = TableName(TableType) Else TblName = NewTableName
        End Select

        'get table structure from dataset

        If Not ResetPDox(TableType) Then Return False
        If DB.CreateTable(TblName, GetTableTemplate(TableType), MustBeUpdateable) Then
            If TableType = enumTableTypes.Working And Not NewTableName.StartsWith(DataPrefix) And Not NewTableName.StartsWith(TempPrefix) Then
                UpdateTrackingRecord(NewTableName, , True)
                If Not ResetPDox(TableType) Then Return False
            ElseIf TableType = enumTableTypes.FIPSCodes Then 'populate with U.S. FIPS codes in text file
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\FIPS Codes.txt", GetTableTemplate(TableType))
            ElseIf TableType = enumTableTypes.WQXMethods Then 'populate with WQX methods
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\WQX Methods.txt", GetTableTemplate(TableType))
            ElseIf TableType = enumTableTypes.Validation Then 'populate with Validation items
                DB.ImportTable(TblName, My.Application.Info.DirectoryPath & "\Validation.txt", GetTableTemplate(TableType))
            End If
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Create all Support tables that don't already exist
    ''' </summary>
    ''' <returns>True if successful</returns>
    Public Function CreateAllSupportTables() As Boolean
        For i As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
            If Not DB.TableExists(TableName(i)) Then
                If Not CreateTable(i) Then Return False
            End If
        Next
        Return True
    End Function

    ''' <summary>
    ''' Project is being closed, so remove all temporary tables and views associated with this user
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub CleanDatabase()
        If DB.DatabaseType = clsDB.enumDBType.Paradox Then
            'if paradox, tempdata and report files may get copied to working or support folders--delete them along with index files
            'also, sometimes all support table get copied over so can do joins--delete these too
            For i As Integer = 1 To 4
                Dim path As String = Choose(i, WorkingDir, MasterDir, SupportDir, SupportDirProject)
                For Each f As String In My.Computer.FileSystem.GetFiles(path, FileIO.SearchOption.SearchTopLevelOnly, "TEMP_*.*")
                    My.Computer.FileSystem.DeleteFile(f)
                Next
                If path.ToUpper <> SupportDir.ToUpper And path.ToUpper <> SupportDirProject.ToUpper Then
                    For j As clsProject.enumTableTypes = clsProject.enumTableTypes.Stations To clsProject.enumTableTypes.Tracking
                        For Each f As String In My.Computer.FileSystem.GetFiles(path, FileIO.SearchOption.SearchTopLevelOnly, TableName(j) & ".*")
                            My.Computer.FileSystem.DeleteFile(f)
                        Next
                    Next
                End If
            Next i
        Else
            'delete temporary tables (and temporary data tables)
            If Not DB.TestConnectionQuick(CONNECTIONTIMEOUT) Then Exit Sub
            If DB.SupportsViews Then
                For Each s As String In DB.GetViewList
                    If s.StartsWith(clsProject.TempPrefix, StringComparison.OrdinalIgnoreCase) Then DB.DropView(s)
                Next
            End If
            For Each s As String In DB.GetTableList
                'GetTableList returns table names in project database and in temp database, but doesn't prefix with database name
                If clsProject.TempPrefix IsNot Nothing AndAlso clsProject.DataPrefix IsNot Nothing Then
                    If s.StartsWith(clsProject.TempPrefix, StringComparison.OrdinalIgnoreCase) Then DB.DropTable(s)
                    If s.StartsWith(clsProject.DataPrefix, StringComparison.OrdinalIgnoreCase) Then DB.DropTable(s)
                    s = clsProject.TempDBName & "." & s
                    If s.StartsWith(clsProject.TempPrefix, StringComparison.OrdinalIgnoreCase) Then DB.DropTable(s)
                    If s.StartsWith(clsProject.DataPrefix, StringComparison.OrdinalIgnoreCase) Then DB.DropTable(s)
                End If
            Next
        End If
    End Sub

    Public Sub DeleteTable(ByVal TableName As String, Optional ByVal DeleteTracking As Boolean = True)
        DB.DropTable(TableName)
        If DeleteTracking AndAlso GetTableType(TableName) = enumTableTypes.Working Then DeleteTrackingRecord(TableName)
    End Sub

    Public Sub RenameTable(ByVal OldName As String, ByVal NewName As String)
        With DB
            If .RenameTable(OldName, NewName, GetTableTemplate(OldName)) Then
                Select Case GetTableType(OldName)
                    Case enumTableTypes.Working : RenameTrackingRecord(OldName, NewName)
                    Case enumTableTypes.Master : MasterTableName = NewName
                End Select
            End If
        End With
    End Sub

    ''' <summary>
    ''' Determine if specified WRDB Stations shapefile is up to date
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsStationShapefileValid() As Boolean
        Dim ShapePath As String = IIf(GisDirectory = "", TempPath, GisDirectory)
        Dim TableName As String = "WRDBSta"
        If Not My.Computer.FileSystem.FileExists(String.Format("{0}\{1}.dbf", ShapePath, TableName)) Then Return False

        Dim checksumDBF As Double = 0, checksumSta As Double = 0
        Dim dbf As New clsDBDBase
        With dbf
            .DatabaseType = clsDB.enumDBType.DBase
            .DataSource = ShapePath
            .CreateConnection()
            If .TableExists(TableName) Then
                Dim lst As Generic.List(Of String) = .GetColumnList(TableName)
                If Not (lst.Contains("LATITUDE") And lst.Contains("LONGITUDE")) Then Return False
            Else
                Return False
            End If
            checksumDBF = TestNull(.ExecuteScalar("SELECT SUM(Latitude)+SUM(Longitude) FROM [{0}]", TableName), 0)
            .Connection.Close()
            .Dispose()
        End With
        If DB.DatabaseType = clsDB.enumDBType.Firebird Or DB.DatabaseType = clsDB.enumDBType.Firebird_Embedded Then 'doesn't support ABS function
            checksumSta = TestNull(DB.ExecuteScalar("SELECT SUM(Latitude)+SUM(Longitude) FROM Stations WHERE Longitude IS NOT NULL and Latitude IS NOT NULL"), 0)
        Else
            checksumSta = TestNull(DB.ExecuteScalar("SELECT SUM(Latitude)+SUM(Longitude) FROM Stations WHERE ABS(Longitude)>1 AND Latitude>1 AND ABS(Longitude)<180 AND Latitude<90"), 0)
        End If
        Return Math.Abs(checksumDBF - checksumSta) < 0.01
    End Function

#Region "Special Routines to Support Paradox tables in multiple directories"

    ''' <summary>
    ''' Reset connection to point to proper Paradox directory (and also see if table is locked)
    ''' </summary>
    ''' <param name="TableName">Table name that subsequent query will reference (if blank, defaults to Working folder); used to determine which folder to use; if supplied will check that file is not locked</param>
    Public Function ResetPDox(Optional ByVal TableName As String = "") As Boolean
        If DB.DatabaseType <> clsDB.enumDBType.Paradox Then Return True
        Dim TableType As enumTableTypes = GetTableType(TableName)
        If ResetPDox(TableType) Then
            If TableName = "" Then Return True
            Dim ParadoxDir As String = ""
            Select Case TableType
                Case enumTableTypes.Working, enumTableTypes.Temporary
                    ParadoxDir = WorkingDir
                Case enumTableTypes.Master
                    ParadoxDir = MasterDir
                Case enumTableTypes.PCodeGrp, enumTableTypes.StaGrp, enumTableTypes.ConvGrp, enumTableTypes.Tracking
                    ParadoxDir = SupportDirProject
                Case Else
                    ParadoxDir = SupportDir
            End Select

            If ParadoxDir.Trim = "" OrElse Not My.Computer.FileSystem.DirectoryExists(ParadoxDir) Then
                Throw New DbSqlException(String.Format("The Paradox directory ('{0}') associated with this project has not been specified or is unavailable; please check the project setup.", ParadoxDir))
                Return False
            End If

            ParadoxDir = ParadoxDir.Trim(New Char() {"\"})
            If String.Equals(DB.DataSource, ParadoxDir, StringComparison.OrdinalIgnoreCase) Then Return True

            DB.DataSource = ParadoxDir
            DB.CreateConnection()

            If My.Computer.FileSystem.FileExists(ParadoxDir & TableName & ".db") Then 'see if it is locked
                Try
                    Dim sw As New IO.FileStream(ParadoxDir & TableName & ".db", IO.FileMode.Open, IO.FileAccess.Read)
                    sw.Close()
                    sw.Dispose()
                    Return True
                Catch ex As Exception
                    Throw New ApplicationException(String.Format("The Paradox file you are trying to access ({0}) is already open by another application.", TableName))
                    Return False
                End Try
            Else
                Return True 'may be trying to create it
            End If
        End If
    End Function

    ''' <summary>
    ''' Reset connection to point to proper Paradox directory
    ''' </summary>
    ''' <param name="TableType">Table type that subsequent query will reference (if blank, defaults to Working)</param>
    Public Function ResetPDox(ByVal TableType As enumTableTypes, Optional ByVal CheckIndices As Boolean = False) As Boolean
        If DB.DatabaseType <> clsDB.enumDBType.Paradox Then Return True

        Dim ParadoxDir As String

        Select Case TableType
            Case enumTableTypes.Temporary 'could be either working or master
                ParadoxDir = WorkingDir
                'todo: handle case where temp may be in master dir
                'if my.Computer.FileSystem.DirectoryExists(workingdir) and my.Computer.FileSystem.FileExists(
            Case enumTableTypes.Working
                ParadoxDir = WorkingDir
            Case enumTableTypes.Master
                ParadoxDir = MasterDir
            Case enumTableTypes.PCodeGrp, enumTableTypes.StaGrp, enumTableTypes.ConvGrp, enumTableTypes.Tracking
                ParadoxDir = SupportDirProject
            Case Else
                ParadoxDir = SupportDir
        End Select

        If ParadoxDir.Trim = "" OrElse Not My.Computer.FileSystem.DirectoryExists(ParadoxDir) Then
            Throw New DbSqlException(String.Format("The Paradox directory ('{0}') associated with this project has not been specified or is unavailable; please check the project setup.", ParadoxDir))
            Return False
        End If

        ParadoxDir = ParadoxDir.Trim(New Char() {"\"})
        If String.Equals(DB.DataSource, ParadoxDir, StringComparison.OrdinalIgnoreCase) Then Return True

        DB.DataSource = ParadoxDir
        DB.CreateConnection()

        If CheckIndices Then

            'See if some corrupted indices exist (this can take a long time)

            Try
                If DB.Connection.State = ConnectionState.Closed Then
                    DB.Connection.Open()
                End If
                DB.Connection.GetSchema("Indexes")
                DB.Connection.Close()
                Return True
            Catch ex As Exception
                If My.Computer.FileSystem.FileExists(Environment.GetFolderPath(Environment.SpecialFolder.System) & "\BDEADMIN.CPL") Then
                    Throw New DbSqlException(String.Format("The directory ({0}) containing the Paradox file you are trying to access contains one or more Paradox tables that are either locked by another application or are corrupt.\n\nClose other database applications or use File | Database Explorer | Compact/Repair to correct file corruption problems.\n\nAnother possible explanation is that you are trying to access a Paradox table on a network drive and the Borland Database Engine (BDE), which is installed on this computer, is not configured correctly. Note that you must have Administrator privileges to configure the BDE.", ParadoxDir))
                Else
                    Throw New DbSqlException(String.Format("The directory ({0}) containing the Paradox file you are trying to access contains one or more Paradox tables that are either locked by another application or are corrupt.\n\nClose other database applications or use File | Database Explorer | Compact/Repair to correct file corruption problems.\n\nAnother possible explanation is that you are trying to access a Paradox table on a network drive which is not possible since you do not have the Borland Database Engine (BDE) installed on this computer. You can download and install the BDE from the WRDB web site or it may be found in the Redist folder of the WRDB program directory. Note that you must have Administrator privileges to install and configure the BDE.", ParadoxDir))
                End If
                Return True
            End Try

        Else
            Return True
        End If

    End Function

    ''' <summary>
    ''' Copy file from one location to another if the destination is older or doesn't exist
    ''' </summary>
    ''' <param name="SourceFile">Full path and filename of file to copy</param>
    ''' <param name="DestFile">Full path and filename of destination file</param>
    ''' <returns>True if copy successful or no need to update; false if error occurred</returns>
    Private Function CopyIfNewer(ByVal SourceFile As String, ByVal DestFile As String) As Boolean
        If Not My.Computer.FileSystem.FileExists(SourceFile) Then Throw New ApplicationException("Source file not found: " & SourceFile) : Return False
        Dim MustUpdate As Boolean = False
        If Not My.Computer.FileSystem.FileExists(DestFile) Then
            MustUpdate = True
        Else
            Dim SourceDate As Date = IO.File.GetLastWriteTime(SourceFile)
            Dim DestDate As Date = IO.File.GetLastWriteTime(DestFile)
            MustUpdate = SourceDate > DestDate
        End If
        If MustUpdate Then
            Try
                My.Computer.FileSystem.CopyFile(SourceFile, DestFile, True)
            Catch ex As Exception
                Throw New ApplicationException(String.Format("Unable to copy file {0} to {1}", SourceFile, DestFile, ex))
                Return False
            End Try
        End If
        Return True
    End Function

    ''' <summary>
    ''' Copy one or more support tables from Paradox support directory to same directory as master or working table being queried
    ''' </summary>
    ''' <param name="DataTabletype">Working or Master</param>
    ''' <param name="TableTypes">Type of support tables needed</param>
    ''' <returns>True if successful; false if error occurred</returns>
    ''' <remarks>This is needed because ADO.Net cannot do join operations across multiple directories like Paradox can</remarks>
    Public Function CopyPDoxSupportTables(ByVal DataTabletype As enumTableTypes, ByVal ParamArray TableTypes() As enumTableTypes) As Boolean
        Select Case DB.DatabaseType
            Case clsDB.enumDBType.Paradox
            Case Else
                Return True
        End Select
        Dim DestDir As String
        If DataTabletype = enumTableTypes.Working Then DestDir = WorkingDir Else DestDir = MasterDir
        For Each tbl As clsProject.enumTableTypes In TableTypes
            Dim SourceDir As String
            Select Case tbl
                Case enumTableTypes.Stations, enumTableTypes.PCodes, enumTableTypes.RCodes, enumTableTypes.QCodes, enumTableTypes.CCodes, enumTableTypes.Branches
                    SourceDir = SupportDir
                Case enumTableTypes.PCodeGrp, enumTableTypes.StaGrp, enumTableTypes.ConvGrp, enumTableTypes.Tracking
                    SourceDir = SupportDirProject
                Case Else
                    Throw New ApplicationException("Invalid table type passed to CopyPDoxSupportTables")
                    Return False
            End Select
            If Not CopyIfNewer(SourceDir & TableName(tbl) & ".db", DestDir & TableName(tbl) & ".db") Then Return False
            If Not CopyIfNewer(SourceDir & TableName(tbl) & ".px", DestDir & TableName(tbl) & ".px") Then Return False
        Next
        Return True
    End Function
#End Region

    Public Function FullTableName(ByVal TableType As enumTableTypes) As String
        Return DB.FullTableName(TableName(TableType))
    End Function

    Public Function FullTableName(ByVal TableName As String) As String
        Return DB.FullTableName(TableName)
    End Function

    '''' <summary>
    '''' Insert data record; RecID value is ignored if not required for data provider; returns True if successful
    '''' </summary>
    'Public Function InsertDataRecord(ByRef trans As DbTransaction, ByVal TableName As String, ByVal RecID As Integer, ByVal StationID As String, ByVal DateTime As Date, ByVal PCode As String, Optional ByVal LEWPct As Object = Nothing, Optional ByVal DepthM As Object = Nothing, Optional ByVal CCode As Object = Nothing, Optional ByVal Agency As Object = Nothing, Optional ByVal RCode As Object = Nothing, Optional ByVal Result As Object = Nothing, Optional ByVal Validated As Boolean = False, Optional ByVal TrackID As Object = Nothing) As Boolean
    '    Dim SQLFormat As String = "INSERT INTO {0} (RecID,Station_ID,Date_Time,PCode,LEW_Pct,Depth_M,CCode,Agency,RCode,Result,Validated,Track_ID) VALUES ({1})"
    '    Dim Values As String = ""
    '    With DB
    '        If Not .IsPrimaryKeyRequired Then
    '            Values = .ValueList(RecID, StationID, DateTime, PCode, LEWPct, DepthM, CCode, Agency, RCode, Result, Validated, TrackID)
    '        Else
    '            SQLFormat.Replace("RecID,", "")
    '            Values = .ValueList(StationID, DateTime, PCode, LEWPct, DepthM, CCode, Agency, RCode, Result, Validated, TrackID)
    '        End If
    '        Return .ExecuteNonQuery(trans, String.Format(SQLFormat, TableName, Values)) > 0
    '    End With
    'End Function

#Region "Routines to keep tracking table synchronized with project"

    ''' <summary>
    ''' When creating tables, entries in the Tracking table may need to be updated
    ''' </summary>
    Public Sub UpdateTrackingRecord(ByVal WorkingTable As String, Optional ByVal RecordCount As Integer = Integer.MinValue, Optional ByVal HasBeenUpdated As Boolean = False)
        Dim da As DbDataAdapter = Nothing
        Dim dt As DataTable = Nothing
        Try
            If GetTableType(WorkingTable) <> enumTableTypes.Working Then Exit Sub

            'load all records into data table
            If Not ResetPDox("Tracking") Then Exit Sub

            If DataSet IsNot Nothing AndAlso DataSet.Tables.Contains("Tracking") Then DataSet.Tables.Remove("Tracking")

            da = DB.AddTable(DataSet, "Tracking", "SELECT * FROM Tracking WHERE ({1}(Temp_DB_Name)='{0}' OR {1}(Temp_DB_Name)='{0}.DB') AND Is_Import=0", WorkingTable.ToUpper, DB.UCase)
            dt = DataSet.Tables("Tracking")

            Dim HasProject As Boolean = DB.GetColumnList("Tracking").Contains("PROJECT_NAME")
            Dim row As DataRow
            Select Case dt.Rows.Count
                Case 0
                    row = dt.NewRow
                    'Case 1
                Case Else
                    row = dt.Rows(0)
                    'Case Else
                    '    Throw New DbSqlException("The Tracking table has more than one record associated with the Working table: " & WorkingTable)
                    '    Exit Sub
            End Select

            With row
                If HasProject Then .Item("Project_Name") = Name 'force it
                SetIfNull(.Item("Temp_DB_Name"), WorkingTable)
                SetIfNull(.Item("Master_DB_Name"), MasterTableName)
                If .Item("Master_DB_Name").ToString.EndsWith(".DB", StringComparison.OrdinalIgnoreCase) Then .Item("Master_DB_Name") = .Item("Master_DB_Name").ToString.Replace(".DB", "").Replace(".db", "")
                'SetIfNull(.Item("Description"), "Automatically updated Tracking record")
                SetIfNull(.Item("Create_Date"), Now)
                SetIfNull(.Item("Create_Init"), Environment.UserName)
                SetIfNull(.Item("Is_Import"), 0)
                If RecordCount <> Integer.MinValue Then .Item("Record_Count") = RecordCount
                If HasBeenUpdated Then .Item("Create_Update") = Now
            End With

            If dt.Rows.Count = 0 Then dt.Rows.Add(row)

            Dim numrows As Integer = da.Update(dt)
        Catch ex As Exception
            'Throw New DbSqlException("Unable to update Tracking table. " & ex.Message, ex)
            Debug.Print("Unable to update tracking table!")
        Finally
            If dt IsNot Nothing Then dt.Dispose()
            If da IsNot Nothing Then da.Dispose()
        End Try
    End Sub

    Public Function GetTrackingRecordCount(ByVal WorkingTable As String) As Integer
        Dim dr As DbDataReader = Nothing
        Try
            ResetPDox("Tracking")
            Dim NumRecords As Integer = TestNull(DB.ExecuteScalar("SELECT Record_Count FROM Tracking WHERE ({1}(Temp_DB_Name)='{0}') AND Is_Import=0", WorkingTable.ToUpper, DB.UCase), -1)
            If NumRecords = 0 Then
                NumRecords = DB.GetRecordCount(WorkingTable)
                UpdateTrackingRecord(WorkingTable, NumRecords)
                Return NumRecords
            ElseIf NumRecords > 0 Then
                Return NumRecords
            Else
                Return -1
            End If
        Catch ex As Exception
            Return 0
        Finally
            If dr IsNot Nothing Then dr.Close()
        End Try
    End Function

    Public Sub GetTrackingRecord(ByVal WorkingTable As String, ByRef RecordCount As Integer, ByRef LastUpdated As Date)
        ResetPDox("Tracking")
        Dim dr As DbDataReader = DB.ExecuteReader("SELECT Record_Count,Create_Update FROM Tracking WHERE ({1}(Temp_DB_Name)='{0}') AND Is_Import=0", WorkingTable.ToUpper, DB.UCase)
        If dr IsNot Nothing AndAlso dr.Read Then
            RecordCount = TestNull(dr.GetValue(0), -1)
            LastUpdated = TestNull(dr.GetValue(1), "1/1/1900")
        Else
            RecordCount = -1
            LastUpdated = "1/1/1900"
        End If
        dr.Close()
    End Sub

    ''' <summary>
    ''' When deleting tables, entries in the Tracking table may need to be update (will delete all import records too)
    ''' </summary>
    Public Sub DeleteTrackingRecord(ByVal WorkingTable As String)
        If Not ResetPDox("Tracking") Then Exit Sub
        DB.ExecuteNonQuery("DELETE FROM Tracking WHERE ({1}(Temp_DB_Name)='{0}' OR {1}(Temp_DB_Name)='{0}.DB')", WorkingTable.ToUpper, DB.UCase)
    End Sub

    ''' <summary>
    ''' When renaming table, entries in the Tracking table must be updated
    ''' </summary>
    Public Sub RenameTrackingRecord(ByVal OldName As String, ByVal NewName As String)
        If Not ResetPDox("Tracking") Then Exit Sub
        DB.ExecuteNonQuery("UPDATE Tracking SET Temp_DB_Name='{1}' WHERE ({2}(Temp_DB_Name)='{0}' OR {2}(Temp_DB_Name)='{0}.DB')", OldName.ToUpper, NewName, DB.UCase)
    End Sub

    ''' <summary>
    ''' Find all Tracking records that match existing tables and fix them; delete for non-existent tables; add for new ones
    ''' </summary>
    ''' <param name="ForceProjectName">If true, all entries will have project name changed to that of current project (UPDATE: will always be set to TRUE)</param>
    ''' <remarks></remarks>
    Public Sub SynchTrackingRecords(ByVal ForceProjectName As Boolean)
        Try
            '9/2010--removed ability to have multiple projects in a database; therefore sync should ALWAYS force projectname to current one
            ForceProjectName = True

            'load all records into data table
            If Not ResetPDox("Tracking") Then Exit Sub

            Dim da As DbDataAdapter = DB.AddTable(DataSet, "Tracking", "SELECT * FROM Tracking")
            Dim dt As DataTable = DataSet.Tables("Tracking")

            Dim HasProject As Boolean = DB.GetColumnList("Tracking").Contains("PROJECT_NAME")

            Dim TableList As Generic.List(Of String) = GetTableList(enumTableTypes.Working)

            'look at each record in tracking table and update with existing table info (delete if no table)

            For Each row As DataRow In dt.Rows
                With row
                    Dim TableName As String = TestNull(.Item("Temp_DB_Name").ToString, "")
                    Dim found As Boolean = False
                    For Each s As String In TableList
                        If s.ToUpper = TableName.ToUpper.Replace(".DB", "") Then
                            found = True
                            'table exists; fix records so all .DB are removed
                            'don't do this--causing too many problems with duplicate keys
                            'If .Item("Temp_DB_Name") <> s Then .Item("Temp_DB_Name") = s
                            If HasProject Then
                                If ForceProjectName And .Item("Project_Name") <> Name Then
                                    .Item("Project_Name") = Name
                                Else
                                    SetIfNull(.Item("Project_Name"), Name)
                                End If
                            End If
                            SetIfNull(.Item("Master_DB_Name"), MasterTableName)
                            If Not .Item("Is_Import") AndAlso .Item("Master_DB_Name").ToString.EndsWith(".DB", StringComparison.OrdinalIgnoreCase) Then .Item("Master_DB_Name") = .Item("Master_DB_Name").ToString.Replace(".DB", "").Replace(".db", "")
                        End If
                    Next
                    If Not found Then 'is in tracking table but does not exist
                        .Delete()
                    End If
                End With
            Next

            da.Update(dt)
            dt.AcceptChanges()

            'see if there are any working tables that are not in the Tracking table--then add

            Dim rowcount As Integer = dt.Rows.Count

            For Each s As String In TableList
                Dim found As Boolean = False
                For i As Integer = 0 To rowcount - 1 'only search through original set of rows
                    Dim row As DataRow = dt.Rows(i)
                    With row
                        Dim TableName As String = TestNull(.Item("Temp_DB_Name").ToString, "")
                        If s.ToUpper = TableName.ToUpper.Replace(".DB", "") Then found = True
                    End With
                Next
                If Not found And GetTableType(s) = enumTableTypes.Working Then 'exists but is not in Tracking table
                    Dim row As DataRow = dt.NewRow
                    With row
                        If HasProject Then .Item("Project_Name") = Name
                        SetIfNull(.Item("Temp_DB_Name"), s)
                        SetIfNull(.Item("Master_DB_Name"), MasterTableName)
                        'SetIfNull(.Item("Description"), "Automatically updated Tracking record")
                        SetIfNull(.Item("Create_Date"), Now)
                        SetIfNull(.Item("Create_Init"), Environment.UserName)
                        SetIfNull(.Item("Is_Import"), False)
                    End With
                    dt.Rows.Add(row)
                End If
            Next

            If Not DB.OpenConnection Then Exit Sub
            Dim numrows As Integer = da.Update(dt)
        Catch ex As Exception
            Throw New ApplicationException("Unable to completely synchronize Tracking records to existing Working tables. It may be necessary to manually edit the Tracking table, resetting the Project Name, Master or Working Table names, and/or deleting records referring to non-existent tables.", ex)
        End Try
    End Sub

#End Region

    Protected Overrides Sub Finalize()
        'note: may make more sense to clear temporary tables, etc. here rather than clsWRDB.close, but will do there so can test for multiple instances of application.
        MyBase.Finalize()
    End Sub

    ''' <summary>
    ''' Raises event so that caller (which may have UI) can report progress
    ''' </summary>
    Private Sub DB_Progress(ByRef DB As clsDB, ByVal Value As Integer, ByVal MaxValue As Integer, ByVal ProgressText As String) Handles DB.Progress
        RaiseEvent Progress(DB, Value, MaxValue, ProgressText)
    End Sub

#Region "Interface Methods for EPDRiv1"
    '    ''' <summary>
    '    ''' Alternative way of initializing because can't include "New" entries in interface used by EPDRiv1
    '    ''' </summary>
    '    ''' <param name="ProjectName">Name of project from WRDB.INI file</param>
    '    ''' <remarks>This also establishes all standard table structures and stored them in empty datatables within a global dataset; it allows tables to be created in provider-independent manner</remarks>
    '    Public Sub iInitialize(ByVal ProjectName As String, Optional ByVal IsTempProject As Boolean = False)
    '        Initialize(ProjectName, IsTempProject)
    '    End Sub

    '    Public Function iFullTableName(ByVal TableName As String) As String
    '        Return FullTableName(TableName)
    '    End Function

    '    ''' <summary>
    '    ''' Return list of tablenames that match list of table types
    '    ''' </summary>
    '    ''' <param name="TableType">List of table types (if empty, returns list of working tables)</param>
    '    ''' <returns>List of table names</returns>
    '    Public Function iGetTableList(ByVal ParamArray TableType() As clsProject.enumTableTypes) As Generic.List(Of String)
    '        Dim tt As enumTableTypes()
    '        ReDim tt(TableType.Length - 1)
    '        For i As Integer = 0 To tt.Length - 1
    '            tt(i) = CType(TableType(i), enumTableTypes)
    '        Next
    '        Return GetTableList(tt)
    '    End Function

    '    Public Function iGetFieldList(ByVal TableName As String, ByVal ColumnName As String, Optional ByVal WhereClause As String = "") As Generic.List(Of String)
    '        Return DB.GetRecordList(TableName, ColumnName, WhereClause)
    '    End Function

    '    Public Function iGetColumnList(ByVal TableName As String) As Generic.List(Of String)
    '        Return DB.GetColumnList(TableName)
    '    End Function

    '    Public Function iExecuteReader(ByVal SqlString As String, ByVal ParamArray Args() As String) As DbDataReader
    '        Return DB.ExecuteReader(String.Format(SqlString, Args))
    '    End Function

    '    Public Function iAddTable(ByRef DataSet As DataSet, ByVal TableName As String, ByVal SqlString As String) As DbDataAdapter
    '        Return DB.AddTable(DataSet, TableName, SqlString)
    '    End Function

    '    Function iCopyPDoxSupportTables(ByVal DataTabletype As clsProject.enumTableTypes, ByVal ParamArray TableTypes() As clsProject.enumTableTypes) As Boolean
    '        Dim tt As enumTableTypes()
    '        ReDim tt(TableTypes.Length - 1)
    '        For i As Integer = 0 To tt.Length - 1
    '            tt(i) = CType(TableTypes(i), enumTableTypes)
    '        Next
    '        Return CopyPDoxSupportTables(CType(DataTabletype, enumTableTypes), tt)
    '    End Function

    '    Public Function iDateTime(ByVal DateTimeValue As DateTime) As String
    '        Return DB.DateTime(DateTimeValue)
    '    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project for a non-client/server data provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ProviderType">Standard provider type</param>
    ''' <param name="DataSource">Name of embedded database file or directory for paradox, dbase, txt, etc. files</param>
    Public Function CreateTempProject(ByVal ProviderType As clsDB.enumDBType, ByVal DataSource As String) As Boolean
        Try
            DB = clsDB.GetDataProvider(ProviderType, DataSource)
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project using the Paradox type that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="_WorkingDir">Working directory for WRDB 4 project</param>
    ''' <param name="_MasterDir">Masterdirectory for WRDB 4 project</param>
    ''' <param name="_SupportDir">Support directory for WRDB 4 project</param>
    ''' <param name="_SupportDirProj">Project support directory for WRDB 4 project</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ByVal _WorkingDir As String, ByVal _MasterDir As String, ByVal _SupportDir As String, ByVal _SupportDirProj As String) As Boolean
        Try
            WorkingDir = _WorkingDir
            MasterDir = _MasterDir
            SupportDir = _SupportDir
            SupportDirProject = _SupportDirProj
            DB = clsDB.GetDataProvider(clsDB.enumDBType.Paradox)
            If Not ResetPDox(enumTableTypes.Working) Then Return False
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project for a client/server provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ProviderType">Standard provider type</param>
    ''' <param name="DataSource">Name of embedded database file or directory for paradox, dbase, txt, etc. files</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ProviderType As clsDB.enumDBType, DataSource As String, DatabaseName As String) As Boolean
        Try
            DB = clsDB.GetDataProvider(ProviderType, DataSource, DatabaseName)
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

    ''' <summary>
    ''' Will create a temporary WRDB project for a client/server provider that will not be saved back to the INI file
    ''' </summary>
    ''' <param name="ProviderType">Standard provider type</param>
    ''' <param name="ExternalDB">Previously configured database</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTempProject(ByVal ExternalDB As clsDB) As Boolean
        Try
            DB = ExternalDB.Clone
        Catch ex As Exception
            Throw
            Return False
        End Try
        Return True
    End Function

#End Region

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If DataSet IsNot Nothing Then DataSet.Dispose() : DataSet = Nothing
                If dsWRDB IsNot Nothing Then dsWRDB.Dispose() : dsWRDB = Nothing
                If DB IsNot Nothing Then DB.Dispose() : DB = Nothing
            End If

            ' TODO: free your own state (unmanaged objects).
            ' TODO: set large fields to null.
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

